﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;
using LogRecord;
using ClassLibraryOverLog;
namespace ClassLibrarySQLite
{
   public  class ClassSQLiteHelper
   {
        protected static void intHlper()
        {

        }

        private static string dbClientNmae = string.Empty;
        private static string dbNmae = string.Empty;
        private static string dbUrlDBpanth = Application.StartupPath + "\\DBFiles\\" + dbClientNmae + "\\" + dbNmae + ".sqlite3";
        private static string dbUrlpanth = Application.StartupPath + "\\DBFiles\\" + dbClientNmae + "\\";

        /// <summary>
        /// 建立数据库的打开连接
        /// </summary>
        /// <param name="ip">连接的客户端的的地址</param>
        /// <param name="connectionKeyStrings">连接的客户端的关键字</param>
        protected static SQLiteConnection CreatDbWithConneton(string ip,string connectionKeyStrings )
        {
           dbClientNmae = ip;
           dbNmae = connectionKeyStrings;  
           dbUrlDBpanth = Application.StartupPath + "\\DBFiles\\" + dbClientNmae + "\\" + dbNmae + ".sqlite3";
           dbUrlpanth = Application.StartupPath + "\\DBFiles\\" + dbClientNmae + "\\";
            try
            {
                if (Directory.Exists(dbUrlpanth) == false)
                {
                    Directory.CreateDirectory(dbUrlpanth);
                }

                if (!File.Exists(dbUrlDBpanth))
                {
                   SQLiteConnection.CreateFile(dbUrlDBpanth);
                }
                SQLiteConnection connection = null;
              //判断连接是否存在
                if (connection == null)
                {
                    connection = new SQLiteConnection("Data Source=" + dbUrlDBpanth + ";Version=3;");
                }
                return connection;       
            }
            catch(Exception we)
            {
                return null;
            }

        }
        /// <summary>
        /// 连接某个数据库
        /// </summary>
        /// <param name="ip"></param>
        /// <param name="connectionKeyStrings"></param>
        /// <returns></returns>
        protected static SQLiteConnection ConnectDB(string ip, string connectionKeyStrings)
        {
            dbClientNmae = ip;
            dbNmae = connectionKeyStrings;
            dbUrlDBpanth = Application.StartupPath + "\\DBFiles\\" + dbClientNmae + "\\" + dbNmae + ".sqlite3";
            dbUrlpanth = Application.StartupPath + "\\DBFiles\\" + dbClientNmae + "\\";
            SQLiteConnection connection = null;
            //判断连接是否存在
            if (connection == null)
            {
                connection = new SQLiteConnection("Data Source=" + dbUrlDBpanth + ";Version=3;");
            }
            return connection;
           
        }
        /// <summary>
        /// 打开某个数据库
        /// </summary>
        /// <param name="connection"></param>
        protected static SQLiteConnection OpenConnection( ref SQLiteConnection connection)
        {
            //如果连接关闭
            if (connection.State == System.Data.ConnectionState.Closed)
            {
                connection.Open();
            }
            //如果连接损坏
            if (connection.State == System.Data.ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }

            return connection;

        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="connection"></param>
        protected static void CloseConnection(ref SQLiteConnection connection)
        {
            //判断连接是否打开或者损坏
            if (connection.State == System.Data.ConnectionState.Open || connection.State == System.Data.ConnectionState.Broken)
            {
                connection.Close();
                connection.Dispose();
            }
        }

        protected static int CreatTable(ref SQLiteConnection connection,string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref connection);
                }
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                return command.ExecuteNonQuery();
            }catch(Exception e)
            {
                ClassOverLogHelper.TraceAndRecordLog(_LogStrClassify.Error, e.Message);
                return -10;
            }
        }

        protected static int InsertData(ref SQLiteConnection connection, string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref connection);
                }
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                return command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                ClassOverLogHelper.TraceAndRecordLog(_LogStrClassify.Error, e.Message);
                return -10;
            }

        }

        protected static int UpdataData(ref SQLiteConnection connection, string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref connection);
                }
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                return command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                ClassOverLogHelper.TraceAndRecordLog(_LogStrClassify.Error, e.Message);
                return -10;
            }

        }
        protected static string ReadData(ref SQLiteConnection connection, string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref connection);
                }
                SQLiteCommand command = new SQLiteCommand(sql, connection);              
                string get = string.Empty;
                using (SQLiteDataReader r = command.ExecuteReader())
                {
                    while (r.Read())
                    {
                        get = r.GetString(0);
                    }
                }
                return get;
            }
            catch (Exception e)
            {
                ClassOverLogHelper.TraceAndRecordLog(_LogStrClassify.Error, e.Message);
                return "";
            }
        }

        protected static int CountTableAllDataNumber(ref SQLiteConnection connection, string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref connection);
                }
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                string get = string.Empty;
                object r = command.ExecuteScalar();                                        
                return Convert.ToInt32(r);

            }
            catch (Exception e)
            {
                ClassOverLogHelper.TraceAndRecordLog(_LogStrClassify.Error, e.Message);
                return -1;
            }
        }

        protected static int GetTableDataCount(ref SQLiteConnection connection, string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref connection);
                }
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                return command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                ClassOverLogHelper.TraceAndRecordLog(_LogStrClassify.Error, e.Message);
                return -10;
            }
        }

        protected static object select(ref SQLiteConnection connection, string sql)
        {
            return "";
        }





    }
}
